USE [TCA_DATABASE]
GO

/****** Object:  Table [dbo].[Account_TBL]    Script Date: 07/13/2013 07:33:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* Account Table */
CREATE TABLE [dbo].[Account_TBL](
    [UserName] [nchar](20) NOT NULL,
    [Password] [nchar](50) NOT NULL,
    [Empl_ID] [nchar](10) NOT NULL,
    [Mail] [nchar](50) NULL,
    [Permission] [nchar](10) NULL,
    [Group_ID] [nchar](10) NULL,
    CONSTRAINT [PK_Account_TBL] PRIMARY KEY CLUSTERED 
    (
        [UserName] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Account_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Account_TBL_CardGroup_TBL] FOREIGN KEY([Group_ID])
REFERENCES [dbo].[CardGroup_TBL] ([Group_Name])
GO

ALTER TABLE [dbo].[Account_TBL] CHECK CONSTRAINT [FK_Account_TBL_CardGroup_TBL]
GO

ALTER TABLE [dbo].[Account_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Account_TBL_Permission_TBL] FOREIGN KEY([Permission])
REFERENCES [dbo].[Permission_TBL] ([Permission_Name])
GO

ALTER TABLE [dbo].[Account_TBL] CHECK CONSTRAINT [FK_Account_TBL_Permission_TBL]
GO

/*Card Group*/

CREATE TABLE [dbo].[CardGroup_TBL](
    [Group_Name] [nchar](10) NOT NULL,
    CONSTRAINT [PK_CardGroup_TBL] PRIMARY KEY CLUSTERED 
    (
        [Group_Name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*Cards_TBL*/
CREATE TABLE [dbo].[Cards_TBL](
    [Card_ID] [nchar](20) NOT NULL,
    [Taxi_Provider_Name] [nchar](15) NOT NULL,
    [Card_Provider_Name] [nchar](20) NOT NULL,
    [Card_Owner] [nchar](20) NULL,
    [Card_Group] [nchar](10) NULL,
    [Card_Department] [nchar](10) NULL,
    [Card_Note] [nchar](50) NULL,
    CONSTRAINT [PK_Cards_TBL_1] PRIMARY KEY CLUSTERED 
    (
        [Card_Provider_Name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Cards_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Cards_TBL_Account_TBL] FOREIGN KEY([Card_Owner])
REFERENCES [dbo].[Account_TBL] ([UserName])
GO

ALTER TABLE [dbo].[Cards_TBL] CHECK CONSTRAINT [FK_Cards_TBL_Account_TBL]
GO

ALTER TABLE [dbo].[Cards_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Cards_TBL_CardGroup_TBL] FOREIGN KEY([Card_Group])
REFERENCES [dbo].[CardGroup_TBL] ([Group_Name])
GO

ALTER TABLE [dbo].[Cards_TBL] CHECK CONSTRAINT [FK_Cards_TBL_CardGroup_TBL]
GO

ALTER TABLE [dbo].[Cards_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Cards_TBL_DEPART_LIST] FOREIGN KEY([Card_Department])
REFERENCES [dbo].[DEPART_LIST] ([DEPARTMEMT_NAME])
GO

ALTER TABLE [dbo].[Cards_TBL] CHECK CONSTRAINT [FK_Cards_TBL_DEPART_LIST]
GO

ALTER TABLE [dbo].[Cards_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Cards_TBL_Taxi_TBL] FOREIGN KEY([Taxi_Provider_Name])
REFERENCES [dbo].[Taxi_TBL] ([Taxi_Provider_Name])
GO

ALTER TABLE [dbo].[Cards_TBL] CHECK CONSTRAINT [FK_Cards_TBL_Taxi_TBL]
GO

/*DEPART_LIST*/
CREATE TABLE [dbo].[DEPART_LIST](
    [DEPARTMEMT_NAME] [nchar](10) NOT NULL,
     CONSTRAINT [PK_DEPART_LIST] PRIMARY KEY CLUSTERED 
    (
        [DEPARTMEMT_NAME] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/*Permission_TBL*/
CREATE TABLE [dbo].[Permission_TBL](
    [Permission_Name] [nchar](10) NOT NULL,
    CONSTRAINT [PK_Permission_TBL] PRIMARY KEY CLUSTERED 
    (
        [Permission_Name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/*Taxi_Bills_TBL*/
CREATE TABLE [dbo].[Taxi_Bills_TBL](
    [Bill_ID] [nchar](10) NOT NULL,
    [Taxi_Provider_Name] [nchar](15) NOT NULL,
    [Bill_date] [date] NOT NULL,
    [Card_Provider_Name] [nchar](20) NOT NULL,
    [Taxi_ID] [nchar](20) NULL,
    [Route] [nchar](50) NULL,
    [Fee] [money] NULL,
    [Others_Fee] [money] NULL,
    [Business] [nchar](10) NULL,
    [Approved] [nchar](10) NULL,
    [Locked] [nchar](10) NULL,
    [Bill_Note] [nchar](100) NULL,
    [Change_by] [nchar](20) NULL,
    [Change_DateTime] [datetime] NULL,
    [Change_type] [nchar](20) NULL,
    CONSTRAINT [PK_Taxi_Bills_TBL] PRIMARY KEY CLUSTERED 
    (
        [Bill_ID] ASC,
        [Taxi_Provider_Name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Taxi_Bills_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Taxi_Bills_TBL_Cards_TBL] FOREIGN KEY([Card_Provider_Name])
REFERENCES [dbo].[Cards_TBL] ([Card_Provider_Name])
GO

ALTER TABLE [dbo].[Taxi_Bills_TBL] CHECK CONSTRAINT [FK_Taxi_Bills_TBL_Cards_TBL]
GO

ALTER TABLE [dbo].[Taxi_Bills_TBL]  WITH CHECK ADD  CONSTRAINT [FK_Taxi_Bills_TBL_Taxi_TBL] FOREIGN KEY([Taxi_Provider_Name])
REFERENCES [dbo].[Taxi_TBL] ([Taxi_Provider_Name])
GO

ALTER TABLE [dbo].[Taxi_Bills_TBL] CHECK CONSTRAINT [FK_Taxi_Bills_TBL_Taxi_TBL]
GO

/*Taxi_TBL*/
CREATE TABLE [dbo].[Taxi_TBL](
    [Taxi_Provider_Name] [nchar](15) NOT NULL,
    CONSTRAINT [PK_Taxi_TBL] PRIMARY KEY CLUSTERED 
    (
        [Taxi_Provider_Name] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO



